package com.guo.core.framework.suanfa;

import cn.hutool.core.lang.Console;
import org.apache.commons.io.FileUtils;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DbTest {

    public static final String JIAO_DB = "D:/MyData/guozl22/IdeaProjects/tms-third/tms-third-service/src/main/resources/db/jiao_db.txt";
    public static final String NEW_SUANFA = "D:/MyData/guozl22/IdeaProjects/tms-third/tms-third-service/src/main/resources/db/new_suanfa.txt";
    public static final String DDL = "D:/MyData/guozl22/IdeaProjects/tms-third/tms-third-service/src/main/resources/db/ddl.txt";
    public static final String DELETE_SQL = "D:/MyData/guozl22/IdeaProjects/tms-third/tms-third-service/src/main/resources/db/delete.txt";
    public static final String ERROR = "D:/MyData/guozl22/IdeaProjects/tms-third/tms-third-service/src/main/resources/db/error.txt";

    @Test
    public void 比较DB文件() throws IOException {
        //读取交大算法距离缓存表-服务平台维度
//        List<String> jiaodaList = this.readTable("distance.db");
        List<String> jiaodaList = FileUtils.readLines(new File(JIAO_DB), "UTF-8");
        StringBuffer es = new StringBuffer("'");
        jiaodaList.forEach(param -> {
            String siteCode = param.substring(2);
            if ("lite_sequence".equals(siteCode)) {
                return ;
            }

            Console.log("服务平台:{}", siteCode);
            // 清理存量数据
            this.truncateTable(siteCode);
            // 清理交大数据中的重复数据
            if (!siteCode.equals("7070595096")) {
                return ;
            }
//            List<Distance> deleteSql = this.deleteDump(siteCode);
//            try {
//                this.exportDeleteSQL(deleteSql, siteCode);
//            } catch (IOException e) {
//                e.printStackTrace();
//            }

            try {
                this.importNewSuanfa(siteCode);
            } catch (IOException e) {
                e.printStackTrace();
            }

        });

    }

    private void importNewSuanfa(String siteCode) throws IOException {
        List<Distance> jiaodaDataList = new ArrayList<>();
        int pageSize = 1000;
        int start = 0;
        do {
            String sql = "select * from t_" + siteCode + " limit "+start+","+pageSize;
            jiaodaDataList = this.readJiaoDaData(sql);
            this.insertNewSuanfa(jiaodaDataList, siteCode, start);

            start = start + pageSize;
        } while (jiaodaDataList.size() > 0);

    }

    private void exportDeleteSQL(List<Distance> deleteSql, String siteCode) throws IOException {
        File f = new File(DELETE_SQL);
        List<String> sqlList =  new ArrayList<>();
        for (Distance distance : deleteSql) {
            String sql = "delete from t_"+siteCode+" where id !="+distance.getId()+" and ad1 ='"+distance.getSrc()+"' and ad2 ='"+distance.getDist()+"';";
            sqlList.add(sql);
        }
        FileUtils.writeLines(f,sqlList,true);
    }

    private void executeUpdate(List<Distance> deleteSql, String siteCode) {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distance.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            for (Distance distance : deleteSql) {
                String sql = "delete from t_"+siteCode+" where id !="+distance.getId()+" and ad1 ='"+distance.getSrc()+"' and ad2 ='"+distance.getDist()+"';";
                Console.log(sql);
                stmt.executeUpdate(sql);
            }

            c.commit();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            Console.error(e);
            System.exit(0);
        }
    }

    private List<Distance> deleteDump(String siteCode) {
        List<Distance> deleteSql = new ArrayList<>();
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distance.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "select max(id) as id,ad1,ad2,distance from t_"+siteCode+" group by ad1||ad2 HAVING count (ad1||ad2) >1;" );
            while ( rs.next() ) {
                int id = rs.getInt("id");
                String  ad1 = rs.getString("ad1");
                String  ad2 = rs.getString("ad2");

                Distance distance = new Distance();
                distance.setId(id);
                distance.setSrc(ad1);
                distance.setDist(ad2);
                deleteSql.add(distance);
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }
        return deleteSql;
    }

    private void truncateTable(String siteCode) {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distances.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            String newSuanfaTable = "Distance_" + siteCode;
            String sql = "DELETE FROM " + newSuanfaTable;
            stmt.executeUpdate(sql);
            c.commit();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        } finally {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    private void insertNewSuanfa(List<Distance> insertList, String siteCode, int start) throws IOException {
        Connection c = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distances.db");
            c.setAutoCommit(false);

            String sql = "INSERT into Distance_"+siteCode+"(id, location1, location2, distance, site_code) values (?,?,?,?,?);";
            PreparedStatement prep = c.prepareStatement(sql);
            for(int j=0; j<insertList.size(); j++) {
                Distance distance = insertList.get(j);
                prep.setInt(1, start + j + 1);
                prep.setString(2, distance.getSrc());
                prep.setString(3, distance.getDist());
//                prep.setBigDecimal(4, distance.getDistance());
                prep.setString(4,distance.getDistance().toString());
                prep.setString(5, siteCode);
                prep.addBatch();
            }

            int[] updateCounts = prep.executeBatch();
            prep.clearBatch();

            c.commit();
            prep.close();
        } catch ( Exception e ) {
            Console.error(e);
            File f = new File(ERROR);
            FileUtils.write(f,e.getMessage(), Charset.forName("UTF8"), true);
        } finally {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    private boolean isExist(Distance jiaoda, List<Distance> newSuanfaDataList) {
        for (Distance newSuanfa : newSuanfaDataList) {
            if (newSuanfa.getSrc().equals(jiaoda.getSrc()) && newSuanfa.getDist().equals(jiaoda.getDist())) {
//                Console.log("新算法:" + newSuanfa);
                return true;
            }
        }

        return false;
    }

    private List<Distance> readNewSuanfaData(String newSuanfaTable) {
        List<Distance> dataList = new ArrayList<>();

        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distances.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "select * from " + newSuanfaTable );
            while ( rs.next() ) {
                String  location1 = rs.getString("location1");
                String  location2 = rs.getString("location2");
                String  distanceStr = rs.getString("distance");
                Distance distance = new Distance();
                distance.setSrc(location1);
                distance.setDist(location2);
                distance.setDistance(new BigDecimal(distanceStr));

                dataList.add(distance);
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }

        return dataList;
    }

    private List<Distance> readJiaoDaData(String sql) {
        List<Distance> dataList = new ArrayList<>();

        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + "distance.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( sql );
            while ( rs.next() ) {
                Integer  id = rs.getInt("id");
                String  ad1 = rs.getString("ad1");
                String  ad2 = rs.getString("ad2");
                String  distanceStr = rs.getString("distance");
                Distance distance = new Distance();
                distance.setId(id);
                distance.setSrc(ad1);
                distance.setDist(ad2);
                distance.setDistance(new BigDecimal(distanceStr));

                dataList.add(distance);
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        } finally {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return dataList;
    }

    private List<String> readTable(String db) {
        List<String> tableList = new ArrayList<>();

        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + db);
            c.setAutoCommit(false);

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "select name from sqlite_master where type='table';" );
            while ( rs.next() ) {
                String  name = rs.getString("name");
                tableList.add(name);
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }

        return tableList;
    }

    @Test
    public void a() throws IOException {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:distances.db");
            c.setAutoCommit(false);

            stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery( "SELECT * FROM Distance_0063;" );
            while ( rs.next() ) {
                int id = rs.getInt("id");
                String  location1 = rs.getString("location1");
                String  location2 = rs.getString("location2");
                String  distance = rs.getString("distance");
                System.out.println( "ID = " + id );
                System.out.println( "NAME = " + location1 );
                System.out.println( "AGE = " + location2 );
                System.out.println( "ADDRESS = " + distance );
                System.out.println();
            }
            rs.close();
            stmt.close();
            c.close();
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }

    }




}
